﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Dapper;
using DBToExcel.Model;

namespace DBToExcel.Code
{
    public class ExcelBuilderDAL
    {
        /// <summary>
        /// 查询模板
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<MTemplate> QueryTemplate(IDbConnection conn, string name)
        {
            string sql = "select * from template ";
            if (!string.IsNullOrEmpty(name))
            {
                sql += string.Format("where name like '%{0}%'", name);
            }

            return conn.Query<MTemplate>(sql).ToList();
        }

        /// <summary>
        /// 查询模板
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public MTemplate QueryTemplateById(IDbConnection conn, string id)
        {
            string sql = "select * from template where id=@id";
            return conn.QueryFirstOrDefault<MTemplate>(sql, new { id = id });
        }

        /// <summary>
        /// 更新模板
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateTemplate(IDbConnection conn, IDbTransaction trans, MTemplate model)
        {
            string sql = "update template set Name=@Name,DataSql=@DataSql,ConnId=@ConnId,TempPlateExcelFile=@TempPlateExcelFile,StartRowIndex=@StartRowIndex,OrgFileName=@OrgFileName,IsTempLate=@IsTempLate where Id=@Id";
            return conn.Execute(sql, model, trans) > 0;
        }

        /// <summary>
        /// 删除模板
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteTemplate(IDbConnection conn,IDbTransaction trans, string id)
        {
            string sql = "delete from template where id=@id";

            return conn.Execute(sql, new { id = id }, trans) > 0;
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddTemplate(IDbConnection conn, IDbTransaction trans, MTemplate model)
        {

            model.Id = Guid.NewGuid().ToString();
            string sql = "insert  into template (Id,Name,ConnId,DataSql,TempPlateExcelFile,StartRowIndex,OrgFileName,IsTempLate) values (@Id,@Name,@ConnId,@DataSql,@TempPlateExcelFile,@StartRowIndex,@OrgFileName,@IsTempLate)";
            return conn.Execute(sql, model, trans) > 0;
        }

        /// <summary>
        /// 查询模板映射
        /// </summary>
        /// <param name="templateid"></param>
        /// <returns></returns>
        public List<MExcelMap> QueryExcelMapById(IDbConnection conn, string templateid)
        {
            string sql = "select  * from excelmap where TemplateId=@id";
            return conn.Query<MExcelMap>(sql,new { id=templateid}).ToList();
        }

        /// <summary>
        ///删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool DeleteExcelMaps(IDbConnection conn, List<string> ids)
        {
            string sql = string.Format("delete from excelmap where id in ('{0}')", string.Join("','", ids));
            return conn.Execute(sql) > 0;
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="maps"></param>
        /// <returns></returns>
        public bool AddExcelMaps(IDbConnection conn, IDbTransaction trans, List<MExcelMap> maps)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append("insert  into excelmap (Id, FieldName,TemplateId,ExcelCellIndex,Script,TempValue) values ");
            List<string> values = new List<string>();
            foreach (var item in maps)
            {
                values.Add(string.Format("('{0}','{1}','{2}','{3}','{4}','{5}')", item.Id, item.FieldName, item.TemplateId, item.ExcelCellIndex,item.Script,item.TempValue));
            }

            sb.Append(string.Join(",", values));

            return conn.Execute(sb.ToString(), null, trans) > 0;
        }


        public bool DeleteExcelMapsByTId(IDbConnection conn, IDbTransaction trans, string tid)
        {
            string sql = "delete from excelmap where templateId=@templateId";
            return conn.Execute(sql, new { templateId = tid }, trans) > 0;
        }

        /// <summary>
        /// 添加连接
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="mDBConn"></param>
        /// <returns></returns>
        public bool AddDBConn(IDbConnection conn, MDBConn mDBConn) {
            mDBConn.Id = Guid.NewGuid().ToString();
            string sql = "insert  into dbconn (Id,Name,FileName,ClassName,ConnStr) values (@Id,@Name,@FileName,@ClassName,@ConnStr)";
            return conn.Execute(sql, mDBConn)>0;
        }

        /// <summary>
        /// 更新连接
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="mDBConn"></param>
        /// <returns></returns>
        public bool UpdateDBConn(IDbConnection conn, MDBConn mDBConn) {
            string sql = "update dbconn set Name=@Name,FileName=@FileName,ClassName=@ClassName,ConnStr=@ConnStr where Id=@Id";
            return conn.Execute(sql, mDBConn)>0;
        }

       /// <summary>
       /// 删除连接
       /// </summary>
       /// <param name="conn"></param>
       /// <param name="id"></param>
       /// <returns></returns>
        public bool DeleteDBConn(IDbConnection conn, string id) {
            string sql = "delete from dbconn where Id=@Id";
            return conn.Execute(sql, new { Id = id }) > 0;
        }

        /// <summary>
        /// 查询全部连接
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public List<MDBConn> QueryAllDBConns(IDbConnection conn) {
            string sql = "select  * from dbconn";
            return conn.Query<MDBConn>(sql).ToList();
        }
    }
}
